USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_update_val') BEGIN
	DROP  FUNCTION  tsql.fn_update_val
	PRINT '<<< DROP FUNCTION tsql.fn_update_val - Completed with SUCCESS >>>'
END
GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.fn_update_val
Type:			User defined function 
Owner:					
Description:	Sorts the elements in a list ASCENDIND/DESCENDING   
Parameters:		@list = list of values comma delimited
				@value = value to update
				@item_no = item number to update 
Dependens on:  	fn_split
Usage:			SELECT [tsql].[fn_update_val] ('7,2,12,23,3,4,5','45',3)
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_update_val&referringTitle=Documentation		
Version:		20120905
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		09/05/2012 - Adrian E Dudau - Function created
================================================================================================*/

CREATE FUNCTION [tsql].[fn_update_val] (@list NVARCHAR(MAX),@value NVARCHAR(MAX),@item_no BIGINT)
RETURNS NVARCHAR(MAX) 
AS

BEGIN /*FUNCTION*/
	DECLARE @Result NVARCHAR(MAX) = '';
	DECLARE @item_value NVARCHAR(MAX) = ''
	DECLARE @rcnt BIGINT = 0 

	SET @RCNT = tsql.fn_count_val(@list,0)

	IF @item_no < 1 OR @item_no > @rcnt  
		SET @result = @list
	ELSE BEGIN 
	DECLARE curs CURSOR FOR 
		SELECT TOP (@rcnt) item_value 
		FROM (SELECT item_value 
				FROM [tsql].[fn_split](@list,',')
				WHERE id < @item_no 
				UNION ALL 
				SELECT 'item_value' = ISNULL(@value,'NULL') 
				UNION ALL 
				SELECT item_value 
				FROM [tsql].[fn_split](@list,',')
				WHERE id > @item_no 
			) dtbl 
		 
		OPEN curs
		FETCH NEXT FROM curs INTO @item_value

		WHILE @@FETCH_STATUS = 0 BEGIN
			SELECT  @result = @result + CASE WHEN @result = '' THEN '' ELSE ',' END + @item_value
			FETCH NEXT FROM curs INTO @item_value
		END

		CLOSE curs
		DEALLOCATE curs
	END 
	
	RETURN @Result 
END /*FUNCTION*/

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_update_val') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_update_val - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_update_val - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


